package cnki.kg.demo;

import cnki.kg.demo.util.ExcelUtil;
import cnki.kg.demo.util.Neo4jUtil;
import cnki.kg.demo.util.StringUtil;
import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;


@SpringBootTest
@WebAppConfiguration
public class ExcelImport {
    @Autowired
    private Neo4jUtil neo4jUtil;
    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Test
    public void contextLoads() {

        String filePath = "D:\\项目\\传统文化\\文化库知识图谱关系梳理20180815.xlsx";
        String domain="ctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    int cellSize = row.getPhysicalNumberOfCells();
                    String keyword = "";
                    String code ="";
                    String name ="";
                    String displayname="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//检索词
                        keyword = cell0.getStringCellValue();
                    }
                    if(row.getCell(1)!=null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(1);//SYS_FLD_CLASS_CODE
                        code = cell1.getStringCellValue();
                    }
                    if(row.getCell(2)!=null){
                        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell2 = row.getCell(2);//SYS_FLD_CLASS_NAME
                        name = cell2.getStringCellValue();
                    }
                    if(row.getCell(3)!=null){
                        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell3 = row.getCell(3);//replace_SYS_FLD_CLASS_NAME
                        displayname = cell3.getStringCellValue();
                    }
                    String[] arr=keyword.split(";");
                    for(String m:arr){
                        HashMap<String, Object> newnode=createnode(domain,m,displayname);
                        String uuid=newnode.get("uuid").toString();
                        String updateSql=String.format("match(n:`%s`) where id(n)=%s set n.codeid='%s',n.code='%s'",domain,uuid,uuid,uuid);
                        neo4jUtil.excuteCypherSql(updateSql);
                        String csql=String.format("MATCH (a:`%s`),(b:`%s`)\n" +
                                "WHERE id(a) = %s AND b.code = '%s'\n" +
                                "merge (a)-[r:RE{name:'包含'}]->(b)\n" +
                                "RETURN r",domain,domain,uuid,code);
                        neo4jUtil.GetGraphRelationShip(csql);
                    }

                }
            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }


    }
    public HashMap<String, Object> createnode(String domain, String name,String displayname) {
        HashMap<String, Object> rss = new HashMap<String, Object>();
        List<HashMap<String, Object>> graphNodeList = new ArrayList<HashMap<String, Object>>();
        try {
            String cypherSql = String.format("MERGE (n:`%s`{name:'%s',nodetype:2,state:1,displayname:'%s'}) return n", domain, name,displayname);
            graphNodeList = neo4jUtil.GetGraphNode(cypherSql);
            if (graphNodeList.size() > 0) {
                rss = graphNodeList.get(0);
                return rss;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return rss;
    }
    @Test
    public void updatKghypernym() {
        //String filePath = "D:\\项目\\传统文化\\文化库知识图谱-包含关系20180910.xlsx";
        String filePath = "D:\\项目\\传统文化\\文化库知识图谱-包含关系20180910.xlsx";
        //String filePath = "D:\\项目\\传统文化\\包含关系-民族饮食.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
           /* String cypherSql = String.format("MATCH (n:`%s`)  set n.hypernym=[] ", domain);
            neo4jUtil.excuteCypherSql(cypherSql);*/
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            List<String> searchwords=new ArrayList<>();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    String keyword = "";
                    String code ="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//检索词
                        keyword = cell0.getStringCellValue();
                    }
                    if(row.getCell(1)!=null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(1);//SYS_FLD_CLASS_CODE
                        code = cell1.getStringCellValue();
                    }
                    System.out.println(code+"-"+keyword);
                   /* if(StringUtil.isNotBlank(keyword)){
                        String cypherSql2 = String.format("MATCH (n:`%s`) where n.code='%s' set n.hypernym='%s' ", domain, code,keyword);
                        neo4jUtil.excuteCypherSql(cypherSql2);
                    }*/
                    if(StringUtil.isNotBlank(keyword)){
                        String[] sArray=keyword.split(";",-1);
                        List<String> sList=Arrays.asList(sArray);
                        List<String> sMap=new ArrayList<>(sList);
                        List<String> formatList=sMap.stream().map(n->String.format("'%s'",n)).collect(Collectors.toList());
                        String arrStr=String.format("[%s]",String.join(",",formatList));
                        String cypherSql2 = String.format("MATCH (n:`%s`) where n.code='%s' set n.hypernym=%s ", domain, code,arrStr);
                        neo4jUtil.excuteCypherSql(cypherSql2);
                    }


                }


            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }


    }
    @Test
    public void updatKgNavname() {
        String filePath = "D:\\项目\\传统文化\\相关分类导航补充20191104.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    String navname = "";
                    String code ="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//SYS_FLD_CLASS_CODE
                        code = cell0.getStringCellValue();
                    }
                    if(row.getCell(2)!=null){
                        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(2);//导航类别属性
                        navname = cell1.getStringCellValue();
                    }
                    System.out.println(code+"-"+navname);
                    if(StringUtil.isNotBlank(navname)){
                        //String cypherSql2 = String.format("MATCH (n:`%s`) where n.code='%s' set n.navname='%s' ", domain, code,navname);
                        String cypherSql2 = String.format("MATCH (n:`%s`) where n.code='%s' set n.alia='%s' ", domain, code,navname);
                        neo4jUtil.excuteCypherSql(cypherSql2);
                    }
                }


            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }
    }
    @Test
    public void updatKgsimilarname() {
        String filePath = "D:\\项目\\传统文化\\20191104同义词补充.xlsx";
        //String filePath = "D:\\项目\\传统文化\\食俗等同义词补充.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    String similar = "";
                    String name ="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//SYS_FLD_CLASS_NAME
                        name = cell0.getStringCellValue();
                    }
                    if(row.getCell(1)!=null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(1);//同义词
                        similar = cell1.getStringCellValue();
                    }
                    System.out.println(name+"-"+similar);
                    if(StringUtil.isNotBlank(similar)){
                        //String cypherSql2 = String.format("MATCH (n:`%s`) where n.name='%s' set n.similar='%s' ", domain, name,similar);
                        String[] sArray=similar.split(";",-1);
                        List<String> sList=Arrays.asList(sArray);
                        List<String> sMap=new ArrayList<>(sList);
                        List<String> formatList=sMap.stream().map(n->String.format("'%s'",n.trim())).collect(Collectors.toList());
                        String arrStr=String.format("[%s]",String.join(",",formatList));
                        String cypherSql2 = String.format("MATCH (n:`%s`) where n.name='%s' set n.similar=%s ", domain, name,arrStr);
                        neo4jUtil.excuteCypherSql(cypherSql2);
                    }
                }


            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }
    }
    @Test
    public void insertDic() {
        //先把检索词和词典里的上位词同步 HYPERNYMCONTENT
        //把 replace_SYS_FLD_CLASS_NAME 同步到图谱
        String filePath = "D:\\项目\\传统文化\\文化库知识图谱-包含关系20180910.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            List<String> searchwords=new ArrayList<>();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    int cellSize = row.getPhysicalNumberOfCells();
                    String keyword = "";
                    String code ="";
                    String name ="";
                    String displayname="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//检索词
                        keyword = cell0.getStringCellValue();
                    }
                    if(row.getCell(1)!=null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(1);//SYS_FLD_CLASS_CODE
                        code = cell1.getStringCellValue();
                    }
                    if(row.getCell(2)!=null){
                        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell2 = row.getCell(2);//SYS_FLD_CLASS_NAME
                        name = cell2.getStringCellValue();
                    }
                    if(row.getCell(3)!=null){
                        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell3 = row.getCell(3);//replace_SYS_FLD_CLASS_NAME
                        displayname = cell3.getStringCellValue();
                    }
                    if(!searchwords.contains(keyword)){
                        searchwords.add(keyword);
                    }

                }
                for (String keyword:searchwords){
                    String[] keyArray=keyword.split(",");
                    for (String w:keyArray){
                        String countSql=String.format("select count(*) from kg_word_item_ctwhctwh where content='%s'",w);
                        Integer totalCount =jdbcTemplate.queryForObject(countSql,Integer.class);
                        if(totalCount<=0){
                            String insertSql=String.format("insert into kg_word_item_ctwhctwh (ID,CONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                                    "values('%s','%s','%s','%s','%s','%s','%s')",UUID.randomUUID().toString(),w,"sa","2019-09-30 14:27:13.000000","sa","2019-09-30 14:27:13.000000",1);
                            jdbcTemplate.execute(insertSql);
                        }

                    }
                }

            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }


    }
    @Test
    public void insertDicSubjectWord() {
        //先把检索词和词典里的上位词同步 HYPERNYMCONTENT
        //把 replace_SYS_FLD_CLASS_NAME 同步到图谱
        String filePath = "D:\\项目\\传统文化\\同义词.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            List<String> searchwords=new ArrayList<>();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    int cellSize = row.getPhysicalNumberOfCells();
                    String CONTENT = "";
                    String RELATIONCONTENT ="";
                    String SYNONYMCONTENT ="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//主题词
                        CONTENT = cell0.getStringCellValue();
                    }
                    if(row.getCell(4)!=null){
                        row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(4);//相关词
                        RELATIONCONTENT = cell1.getStringCellValue();
                    }
                    if(row.getCell(5)!=null){
                        row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell2 = row.getCell(5);//等同词
                        SYNONYMCONTENT = cell2.getStringCellValue();
                    }
                    //检查主题词表是否包含主题词
                    //如果主题词存在，检查同义词是否和表格里相同，如果不同这追加，相同则跳过
                    //如果主题词不存在，则追加本条记录
                    String sql=String.format("select * from subject_word_item where CONTENT='%s'",CONTENT);
                    List<Map<String, Object>> result=jdbcTemplate.queryForList(sql);
                    if(result!=null&&result.size()>0){
                        for (Map<String, Object> mp:result){
                            String synonymWord=mp.get("SYNONYMCONTENT").toString();
                            if(StringUtil.isBlank(SYNONYMCONTENT)||SYNONYMCONTENT.equals(synonymWord))continue;
                            List<String> ws=new ArrayList<>();
                            if(StringUtil.isNotBlank(synonymWord)){
                                List<String> arrList=Arrays.asList(synonymWord.split(","));
                                ws = new ArrayList(arrList);
                            }
                            List<String> nws=new ArrayList<>();
                            if(StringUtil.isNotBlank(SYNONYMCONTENT)){
                                List<String> arrList=Arrays.asList(SYNONYMCONTENT.split(","));
                                nws= new ArrayList(arrList);
                            }
                            if(nws!=null&&nws.size()>0){
                                ws.addAll(nws);
                            }

                            List<String> wsd=ws.stream().distinct().collect(Collectors.toList());
                            String newword=String.join(",",wsd);
                            String updatemySql=String.format("update subject_word_item set SYNONYMCONTENT='%s' where content='%s'",newword,CONTENT);
                            jdbcTemplate.execute(updatemySql);
                        }
                    }else{
                        String insertSql=String.format("insert into subject_word_item (ID,DICID,CONTENT,RELATIONCONTENT,SYNONYMCONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                                "values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",UUID.randomUUID().toString(),"DE8FFA45-D988-44CF-ACC2-5A20BFCC489E",CONTENT,RELATIONCONTENT,SYNONYMCONTENT,"sa","2019-09-30 14:27:13.000000","sa","2019-09-30 14:27:13.000000",1);
                        jdbcTemplate.execute(insertSql);
                    }
                }
            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }
    }
    @Test
    public void respair(){
        String sql="SELECT CONTENT from subject_word_item GROUP BY CONTENT HAVING count(*)>1";
        List<Map<String, Object>> result=jdbcTemplate.queryForList(sql);
        if(result!=null&&result.size()>0) {
            for (Map<String, Object> mp : result) {
                String content=mp.get("CONTENT").toString();
                String sql2=String.format("SELECT ID from subject_word_item WHERE CONTENT='%s'",content);
                List<Map<String, Object>> result2=jdbcTemplate.queryForList(sql2);
                if(result2!=null&&result2.size()>0) {
                    List<String> deleteIds=new ArrayList<>();
                    List<String> ids= result2.stream().map(n->n.get("ID").toString()).collect(Collectors.toList());
                    if(ids.size()>1){
                        ids.remove(ids.size()-1);
                    }
                    deleteIds=ids.stream().map(n->String.format("'%s'",n)).collect(Collectors.toList());
                    String idstr=String.join(",",deleteIds);
                    String deletesql = String.format("DELETE from subject_word_item where ID in (%s)", idstr);
                    jdbcTemplate.execute(deletesql);
                }
            }
        }
    }
    @Test
    public void respairDic() {
        String sql = "SELECT SYS_FLD_CLASS_NAME FROM WHBT_CLS ";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        //System.out.println(result);
        for (Map<String, Object> item : result) {
            String w=item.get("SYS_FLD_CLASS_NAME")!=null?item.get("SYS_FLD_CLASS_NAME").toString():"";
            if(StringUtil.isNotBlank(w)){
                String countSql = String.format("select count(*) from kg_word_item_ctwhctwh where content='%s'", w);
                Integer totalCount = jdbcTemplate.queryForObject(countSql, Integer.class);
                if (totalCount <= 0) {
                    System.out.println(w);
                    String insertSql = String.format("insert into kg_word_item_ctwhctwh (ID,CONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                            "values('%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), w, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                    jdbcTemplate.execute(insertSql);
                }
            }

        }
    }
    @Test
    public void respairdata() {
        String sql = "SELECT SYS_FLD_CLASS_CODE,SYS_FLD_CLASS_NAME,`同义词（英文半角隔开）` from whbt_cls ";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> item : result) {
            String className=item.get("sys_fld_class_name")!=null?item.get("sys_fld_class_name").toString():"";
            String classCode=item.get("sys_fld_class_code")!=null?item.get("sys_fld_class_code").toString():"";
            String words=item.get("同义词（英文半角隔开）")!=null?item.get("同义词（英文半角隔开）").toString():"";
            System.out.println(className+"-"+classCode+"-"+words);
            if(StringUtil.isNotBlank(words)){
                String[] wordArray=words.split(";");
                for(String w:wordArray){
                    String countSql = String.format("select count(*) from kg_word_item_ctwhctwh where content='%s'", w);
                    Integer totalCount = jdbcTemplate.queryForObject(countSql, Integer.class);
                    if (totalCount <= 0) {
                        System.out.println(w);
                        String insertSql = String.format("insert into kg_word_item_ctwhctwh (ID,CONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                                "values('%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), w, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                        jdbcTemplate.execute(insertSql);
                    }
                }

            }


        }
    }
    @Test
    public void addDicData() {
        String sql = "SELECT SYS_FLD_CLASS_CODE,SYS_FLD_CLASS_NAME,`同义词（英文半角隔开）` from whbt_cls ";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> item : result) {
            String className=item.get("sys_fld_class_name")!=null?item.get("sys_fld_class_name").toString():"";
            if(StringUtil.isNotBlank(className)){
                String countSql = String.format("select count(*) from kg_word_item_ctwhctwh where content='%s'", className);
                Integer totalCount = jdbcTemplate.queryForObject(countSql, Integer.class);
                if (totalCount <= 0) {
                    System.out.println(className);
                    String insertSql = String.format("insert into kg_word_item_ctwhctwh (ID,CONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                            "values('%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), className, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                    jdbcTemplate.execute(insertSql);
                }
            }


        }
    }
    @Test
    public void addSimilarDicData() {
        String filePath = "D:\\项目\\传统文化\\近义词更新.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    String similar = "";
                    String name ="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//SYS_FLD_CLASS_NAME
                        name = cell0.getStringCellValue();
                    }
                    if(row.getCell(1)!=null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(1);//同义词
                        similar = cell1.getStringCellValue();
                    }

                    if(StringUtil.isNotBlank(similar)){
                        String[] sArray=similar.split(";");
                       for(String sw:sArray){
                           System.out.println(name+"-"+sw);
                           String countSql = String.format("select count(*) from kg_word_item_ctwhctwh where content='%s'", sw);
                           Integer totalCount = jdbcTemplate.queryForObject(countSql, Integer.class);
                           if (totalCount <= 0) {
                               System.out.println(sw);
                               String insertSql = String.format("insert into kg_word_item_ctwhctwh (ID,CONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                                       "values('%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), sw, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                               jdbcTemplate.execute(insertSql);
                           }
                       }
                    }
                }


            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }

    }

    @Test
    public void addhypernymDicData() {
        String filePath = "D:\\项目\\传统文化\\文化库知识图谱-包含关系20180910.xlsx";
        String domain="zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            List<String> searchwords=new ArrayList<>();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if(j==0) continue;
                    Row row = sheet.getRow(j);
                    String keyword = "";
                    String code ="";
                    if(row.getCell(0)!=null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//检索词
                        keyword = cell0.getStringCellValue();
                    }
                    if(row.getCell(1)!=null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell1 = row.getCell(1);//SYS_FLD_CLASS_CODE
                        code = cell1.getStringCellValue();
                    }
                    //System.out.println(code+"-"+keyword);
                    if(StringUtil.isNotBlank(keyword)){
                        String[] sArray=keyword.split(",");
                        for(String sw:sArray){
                            System.out.println(sw);
                            String countSql = String.format("select count(*) from kg_word_item_ctwhctwh where content='%s'", sw);
                            Integer totalCount = jdbcTemplate.queryForObject(countSql, Integer.class);
                            if (totalCount <= 0) {
                                String insertSql = String.format("insert into kg_word_item_ctwhctwh (ID,CONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                                        "values('%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), sw, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                                jdbcTemplate.execute(insertSql);
                            }
                        }
                    }

                }


            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }


    }
    @Test
    public void respairDicSubjectWord() {
        //检查主题词表是否包含主题词
        //如果主题词存在，检查同义词是否和表格里相同，如果不同这追加，相同则跳过
        //如果主题词不存在，则追加本条记录
        String sql = "SELECT SYS_FLD_CLASS_CODE,SYS_FLD_CLASS_NAME,`同义词（英文半角隔开）` from whbt_cls ";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> item : result) {
            String className = item.get("sys_fld_class_name") != null ? item.get("sys_fld_class_name").toString() : "";
            String classCode = item.get("sys_fld_class_code") != null ? item.get("sys_fld_class_code").toString() : "";
            String words = item.get("同义词（英文半角隔开）") != null ? item.get("同义词（英文半角隔开）").toString() : "";
            System.out.println(className + "-" + classCode + "-" + words);
            if (StringUtil.isNotBlank(className)&&StringUtil.isNotBlank(words)) {
                List<String> arrList2 = Arrays.asList(words.split(";"));
                List<String> arrList3= new ArrayList(arrList2);
                String oldSyncStr= String.join(",",arrList3);
                String sql2 = String.format("select * from subject_word_item where CONTENT='%s'", className);
                List<Map<String, Object>> result2 = jdbcTemplate.queryForList(sql2);
                if (result2 != null && result2.size() > 0) {
                    for (Map<String, Object> mp : result2) {
                        String synonymWord = mp.get("SYNONYMCONTENT").toString();
                        if(oldSyncStr.equals(synonymWord)) continue;
                        List<String> ws = new ArrayList<>();
                        if (StringUtil.isNotBlank(synonymWord)) {
                            List<String> arrList = Arrays.asList(synonymWord.split(","));
                            ws = new ArrayList(arrList);
                        }
                        List<String> nws = new ArrayList<>();
                        if (StringUtil.isNotBlank(words)) {
                            List<String> arrList = Arrays.asList(words.split(";"));
                            nws = new ArrayList(arrList);
                        }
                        if (nws != null && nws.size() > 0) {
                            ws.addAll(nws);
                        }
                        List<String> wsd = ws.stream().distinct().collect(Collectors.toList());
                        String newword = String.join(",", wsd);
                        String updatemySql = String.format("update subject_word_item set SYNONYMCONTENT='%s' where content='%s'", newword, className);
                        jdbcTemplate.execute(updatemySql);
                    }
                }
            }else{
                String SYNONYMCONTENT="";
                if (StringUtil.isNotBlank(words)) {
                    List<String> arrList = Arrays.asList(words.split(";"));
                    SYNONYMCONTENT=String.join(",",arrList);
                    String insertSql = String.format("insert into subject_word_item (ID,DICID,CONTENT,RELATIONCONTENT,SYNONYMCONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                            "values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), "DE8FFA45-D988-44CF-ACC2-5A20BFCC489E", className, "", SYNONYMCONTENT, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                    jdbcTemplate.execute(insertSql);
                }

            }
        }
    }

    @Test
    public void addDicSubjectWord() {
        //检查主题词表是否包含主题词
        //如果主题词存在，检查同义词是否和表格里相同，如果不同这追加，相同则跳过
        //如果主题词不存在，则追加本条记录
        String sql = "SELECT SYS_FLD_CLASS_CODE,SYS_FLD_CLASS_NAME,`同义词（英文半角隔开）` from whbt_cls ";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> item : result) {
            String className = item.get("sys_fld_class_name") != null ? item.get("sys_fld_class_name").toString() : "";
            String classCode = item.get("sys_fld_class_code") != null ? item.get("sys_fld_class_code").toString() : "";
            String words = item.get("同义词（英文半角隔开）") != null ? item.get("同义词（英文半角隔开）").toString() : "";

            if (StringUtil.isNotBlank(className)) {
                String SYNONYMCONTENT="";
                if (StringUtil.isNotBlank(words)) {
                    List<String> arrList = Arrays.asList(words.split(";"));
                    SYNONYMCONTENT=String.join(",",arrList);
                }
                System.out.println(className + "-" + classCode + "-" + words);
                String insertSql = String.format("insert into subject_word_item2 (ID,DICID,CONTENT,RELATIONCONTENT,SYNONYMCONTENT,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,STATE) " +
                        "values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')", UUID.randomUUID().toString(), "DE8FFA45-D988-44CF-ACC2-5A20BFCC489E", className, "", SYNONYMCONTENT, "sa", "2019-09-30 14:27:13.000000", "sa", "2019-09-30 14:27:13.000000", 1);
                jdbcTemplate.execute(insertSql);
            }
        }
    }

    @Test
    public void AddAlia() {
        String filePath = "D:\\项目\\传统文化\\新增导航类别属性-谭超20200424.xlsx";
        String domain = "zhctwh";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        try {
            in = new FileInputStream(file);
            Workbook workbook = null;
            if (ExcelUtil.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            List<String> searchwords = new ArrayList<>();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowSize = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowSize; j++) {
                    if (j == 0) continue;
                    Row row = sheet.getRow(j);
                    int cellSize = row.getPhysicalNumberOfCells();
                    String code = "";
                    String alia = "";
                    if (row.getCell(0) != null) {
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell0 = row.getCell(0);//主题词
                        code = cell0.getStringCellValue();
                    }
                    if (row.getCell(5) != null) {
                        row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                        Cell cell2 = row.getCell(5);//等同词
                        alia = cell2.getStringCellValue();
                    }
                    String cypher=String.format("match (n:`zhctwh`) where n.code='%s' set n.alia='%s'",code,alia);
                    neo4jUtil.excuteCypherSql(cypher);
                    cypher=String.format("match (n:`zhctwh`) where n.code='%s' return n",code);
                    List<HashMap<String, Object>> nodes=neo4jUtil.GetGraphNode(cypher);
                    System.out.println(JSON.toJSONString(nodes));
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
